﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DreamIn.Structures.ServerDatabase.Entities;
using MySql.Data.MySqlClient;

namespace DreamIn.Structures.ServerDatabase.DataAccessLayer
{
    class AlbumDAL
    {
        public List<AlbumEntity> GetAlbums()
        {
            MySqlConnection connection = DALHelper.Connection;
            try
            {
                MySqlCommand command = connection.CreateCommand();
                MySqlDataReader Reader;

                command.CommandText = "select  id_album,name,fname,path,id_user from albums";
                connection.Open();
                Reader = command.ExecuteReader();
                List<AlbumEntity> result = new List<AlbumEntity>();
                while (Reader.Read())
                {
                    result.Add(new AlbumEntity() { id_album = Reader.GetInt16(0),name = Reader.GetString(1), fname = Reader.GetString(2), path = Reader.GetString(3), id_user=Reader.GetInt16(4) });
                }
                Reader.Close();
                return result;
            }
            finally
            {
                connection.Close();
            }
        }

        public void AddAlbum(String name,String fname,String path, int id_user)
        {
            MySqlConnection connection = DALHelper.Connection;
            try
            {
                MySqlCommand command = connection.CreateCommand();
              
                connection.Open();
                command.CommandText = "INSERT INTO albums (name, fname, path, id_user) VALUES" + "('" + name + "','" + fname + "','" + path + "','" + id_user + "')";
                command.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }

        public void DeleteAlbum(AlbumEntity album)
        {
            MySqlConnection connection = DALHelper.Connection;
            try
            {
                MySqlCommand command = connection.CreateCommand();

                connection.Open();
                command.CommandText = "DELETE FROM albums WHERE id_album="+album.id_album;
                command.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }

        public void UpdateAlbum(int id_album, String name, String fname, String path, int id_user)
        {
            MySqlConnection connection = DALHelper.Connection;
            try
            {
                MySqlCommand command = connection.CreateCommand();

                connection.Open();
                command.CommandText = "UPDATE albums SET name='" + name + "', fname='" + fname + "', path='" + path + "', id_user='" + id_user + "' WHERE id_album=" + id_album;
                command.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
